from pylons import config
from pylons import session
from sqlalchemy import Column, MetaData, Table
from sqlalchemy.types import *
from sqlalchemy.schema import *
from sqlalchemy.sql import or_, and_
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm import scoped_session, sessionmaker, backref
from sqlalchemy.exceptions import InvalidRequestError

from datetime import datetime

import logging
log = logging.getLogger(__name__)

Session = scoped_session(sessionmaker(autoflush=True, transactional=True,
                                      bind=config['pylons.g'].sa_engine))
meta = MetaData()
plugin_meta = MetaData()

MD5Type = String(32)

class ObjBase(object):
    def __init__(self, **kw):
        self.__dict__.update(kw)

    @classmethod
    def get(klass, id):
        return Session.query(klass).get(id)

    @classmethod
    def getAll(klass):
        return Session.query(klass).all()

    @classmethod
    def query(klass):
        return Session.query(klass)

    @classmethod
    def count(klass):
        return Session.query(klass).count()

    def save(self):
        Session.save_or_update(self)
        Session.commit()

    def delete(self):
        Session.delete(self)
        Session.commit()

    def _getjson(self):
        return dict([(c.name, getattr(self, c.name)) for c in self.c])


UsersTable = Table('users', meta,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('username', String(50)),
    #Column('password', MD5Type),
    Column('realname', String(255)),
    Column('email', String(255)),
)

class User(ObjBase):
    @classmethod
    def getByUsername(klass, username):
        try:
            return Session.query(klass).filter(klass.username==username).one()
        except InvalidRequestError:
            return None

GroupsTable = Table('groups', meta,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('name', String(50)),
    Column('description', String(255)),
)

class Group(ObjBase):
    pass

UserGroupsTable = Table('user_groups', meta, 
    Column('user_id', Integer, primary_key=True, autoincrement=False),
    Column('group_id', Integer, primary_key=True, autoincrement=False),
)   

class UserGroup(ObjBase):
    pass


UserPermissionsTable = Table('user_permissions', meta,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('user_id', Integer, ForeignKey(UsersTable.c.id)),
    Column('thing', String(50)),
    Column('action', String(50)),
)

class UserPermission(ObjBase):
    @classmethod
    def get(klass, user, thing, action):
        try:
            return (Session.query(klass)
                    .filter(klass.user_id==(user.id if user else 0))
                    .filter(klass.thing==thing)
                    .filter(klass.action==action)
                    .one()
            )
        except InvalidRequestError, e:
            print e
            return None

    @classmethod
    def getAll(klass):
        return (Session.query(klass)
                .order_by(klass.thing)
                .order_by(klass.action)
        )

    @classmethod
    def permission(klass, user, thing, action):
        return (Session.query(klass)
                .filter(klass.user_id==(user.id if user else 0))
                .filter(klass.thing==thing)
                .filter(klass.action==action)
                .count()
        )

    @classmethod 
    def grant(klass, user, thing, action):
        if klass.permission(user, thing, action):
            return 
        p = klass(user_id=(user.id if user else 0), thing=thing, action=action)
        p.save()
        return p

    @classmethod
    def revoke(klass, user, thing, action):
        log.info('revoking %s %s %s' % (user, thing, action))
        p = klass.get(user, thing, action)
        if not p: 
            log.info('didnt have perm anyway!')
            return 
        p.delete()


"""
WorkspacesTable = Table('workspaces', meta,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('title', String(255)),
    Column('creator_id', Integer, ForeignKey('users.id')),
    Column('description', Text),
)

class Workspace(ObjBase):
    pass
"""

PagesTable = Table('pages', meta,
    Column('id', Integer, primary_key=True, autoincrement=True),
    #Column('parent_id', Integer, ForeignKey('pages.id')),
    #Column('workspace_id', Integer, ForeignKey('workspaces.id')),
    Column('title', String(255)),
    Column('author_id', Integer, ForeignKey('users.id')),
    Column('created', DateTime, default=datetime.now),
    Column('deleted', Boolean, default=False),
)


class Page(ObjBase):
    @classmethod
    def getByTitle(klass, title):
        title = title.replace('_', ' ')

        q = (Session.query(klass)
                .filter(klass.title==title)
                .filter(klass.deleted==False)
        )

        try:
            return q.one()
        except InvalidRequestError:
            return None

    @classmethod
    def getAll(klass):
        return (Session.query(klass)
            .filter(klass.deleted==False)
            .order_by(klass.title)
        )

    def delete(self):
        self.deleted = True
        self.save()

    def getRevisions(self):
        return (Session.query(PageRevision)
            .filter(PageRevision.page_id==self.id)
            .order_by(PageRevision.revision.desc())
            .all()
        )

    def getLatest(self):
        return (Session.query(PageRevision)
            .filter(PageRevision.page_id==self.id)
            .order_by(PageRevision.revision.desc())
            .first()
        )

    def getRevision(self, revision):
        try:
            return (Session.query(PageRevision)
                .filter(PageRevision.page_id==self.id)
                .filter(PageRevision.revision==revision)
                .one()
            )
        except InvalidRequestError:
            return None

    def urlTitle(self):
        return self.title.replace(' ', '_')

    @classmethod
    def add(klass, title, user, body, comment=None):
        title = title.strip()
        if not title:
            raise ValueError('Title may not be empty')

        comment = comment.strip() or None
        
        page = klass.getByTitle(title)
        if page is None:
            page = klass(title=title, author_id=user.id if user else None)
            page.save()
            rev = 0
        else:
            latest = page.getLatest()
            if latest.body == body:
                return (page, latest)
            rev = latest.revision
            if title != page.title:
                page.title = title
                page.save()

        page_rev = PageRevision(
            page_id=page.id,
            title=title,
            revision=rev + 1,
            body=body,
            author_id=user.id if user else None,
            comment=comment,
        )
        page_rev.save()

        return (page, page_rev)    
        

PageRevisionTable = Table('page_revisions', meta, 
    Column('page_id', Integer, ForeignKey('pages.id'),
            primary_key=True, autoincrement=False),
    Column('revision', Integer, primary_key=True, autoincrement=False),
    Column('title', String(255), nullable=False),
    Column('body', Text, nullable=False),
    Column('comment', Text),
    Column('author_id', Integer, ForeignKey('users.id')),
    Column('created', DateTime, default=datetime.now),
)

class PageRevision(ObjBase):
    def render(self):
        formatter = config['mtp.formatter']
        return formatter(self.body)


PluginsTable = Table('plugins', meta, 
    Column('name', String(255), primary_key=True),
    Column('configured', Boolean),
)

class Plugin(ObjBase):
    pass


mapper(User, UsersTable)
mapper(Group, GroupsTable)
mapper(UserGroup, UserGroupsTable)
mapper(UserPermission, UserPermissionsTable, properties={
    'user': relation(User),
})

#mapper(Workspace, WorkspacesTable, properties={
#    'creator': relation(User),
#})

mapper(Page, PagesTable, properties={
    #'parent': relation(Page),
    #'workspace': relation(Workspace),
    'author': relation(User),
    'revisions': relation(PageRevision),
})

mapper(PageRevision, PageRevisionTable, properties={
    'page': relation(Page),
    'author': relation(User),
}, order_by=PageRevisionTable.c.revision.desc())

mapper(Plugin, PluginsTable)











